# coding=utf-8
from ConfigParser import ConfigParser
import sqlite3 as sqlite
import math

class TechDao():
    
    def __init__(self):
        self.setting=ConfigParser()
        self.setting.read("setup/setting.ini")
        self.cn=sqlite.connect(self.setting.get("common","dbpath"))
    
    def insert(self,title,content):
        sql="insert into Tech(title,inserttime,content) values(?,datetime('now'),?)"
        cur=self.cn.cursor()
        i=cur.execute(sql,[title,content])
        self.cn.commit()
        cur.close()
        return i
    
    def update(self,id,title,content):
        sql="update Tech set title=?,updatetime=datetime('now'),content=? where id=?"
        cur=self.cn.cursor()
        i=cur.execute(sql,[title,content,id])
        self.cn.commit()
        cur.close()
        return i
    
    def delete(self,id):
        sql="delete from Tech where id=?"
        cur=self.cn.cursor()
        i=cur.execute(sql,[id])
        self.cn.commit()
        cur.close()
        return i
    
    def select(self,where):
        sql="select id,title,inserttime,updatetime,content from Tech where 1=1 "
        sql+=where
        sql+=" order by id desc"
        cur=self.cn.cursor()
        cur.execute(sql)
        rows=cur.fetchall()
        cur.close()      
        return rows 
    
    def pagecount(self,pagesize,where):
        sql="select count(*) c from Tech where 1=1 "
        sql+=where
        cur=self.cn.cursor()
        cur.execute(sql)
        rows=cur.fetchall()
        cur.close()
        pagecount=math.ceil(float(rows[0][0])/pagesize)
        return pagecount
    
    def selectpage(self,pagesize,page,where):
        sql="select id,title,inserttime,updatetime,content from Tech where 1=1 "
        sql+=where
        sql+=" order by id desc limit "+str(pagesize)+" offset "+str((page-1)*pagesize)        
        cur=self.cn.cursor()
        cur.execute(sql)
        rows=cur.fetchall()
        cur.close()      
        return rows        

